package com.example.score.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.score.entity.ExamDetail;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Param;

import java.time.LocalDateTime;
import java.util.List;

@Mapper
public interface ExamDetailMapper extends BaseMapper<ExamDetail> {

    @Select("SELECT * FROM t_exam_detail WHERE id = #{id}")
    ExamDetail selectById(@Param("id") Long id);

    @Select("SELECT * FROM t_exam_detail WHERE exam_id = #{examId} LIMIT 1")
    ExamDetail selectByExamId(@Param("examId") Long examId);

    @Select("SELECT e.* FROM t_exam_detail e " +
            "INNER JOIN t_class_info ci ON e.id = ci.exam_detail_id " +
            "WHERE ci.class_id = #{classId} and e.subject_onlyid = #{subjectOnlyid} " +
            "ORDER BY e.created_time DESC " +
            "LIMIT #{limit}")
    List<ExamDetail> selectRecentExams(@Param("classId") Long classId, @Param("limit") Integer limit,@Param("subjectOnlyid") Long subjectOnlyid);

    /**
     * 根据日期范围查询考试记录
     */
    @Select("SELECT ed.* FROM t_exam_detail ed " +
            "LEFT JOIN t_class_info ci ON ed.id = ci.exam_detail_id " +
            "LEFT JOIN t_exam_base eb ON eb.exam_id = ed.exam_id " +
            "WHERE ci.class_id = #{classId} " +
            "AND ci.class_firstpushscore_time BETWEEN #{startDate} AND #{endDate} " +
            "ORDER BY eb.start_time DESC")
    List<ExamDetail> selectExamsByDateRange(
            @Param("classId") Long classId,
            @Param("startDate") LocalDateTime startDate,
            @Param("endDate") LocalDateTime endDate);

    /**
     * 根据日期范围统计考试记录数量
     */
    @Select("SELECT COUNT(*) FROM t_exam_detail ed " +
            "LEFT JOIN t_class_info ci ON ed.id = ci.exam_detail_id " +
            "WHERE ci.class_id = #{classId} " +
            "AND ed.created_time BETWEEN #{startDate} AND #{endDate}")
    long countExamsByDateRange(
            @Param("classId") Long classId,
            @Param("startDate") LocalDateTime startDate,
            @Param("endDate") LocalDateTime endDate);

    /**
     * 根据时间范围和学科ID查询考试
     */
    @Select("<script>"
            + "SELECT distinct ed.* FROM t_exam_detail ed "
            + "LEFT JOIN t_exam_base eb ON ed.exam_id = eb.exam_id "
            + "LEFT JOIN t_class_info ci ON ci.exam_detail_id = ed.id "
            + "WHERE eb.start_time >= #{startTime} AND eb.start_time &lt;= #{endTime} "
            + "AND ed.subject_onlyid = #{subjectId} "
            + "AND ci.class_id IN "
            + "<foreach collection='classIds' item='classId' open='(' separator=',' close=')'>"
            + "#{classId}"
            + "</foreach>"
            + "</script>")
    List<ExamDetail> selectByDateRangeAndSubject(
            @Param("startTime") LocalDateTime startTime,
            @Param("endTime") LocalDateTime endTime,
            @Param("subjectId") Long subjectId,
            @Param("classIds") List<Long> classIds);


    @Select("<script>"
            + "SELECT * FROM t_exam_detail WHERE "
            + "id IN "
            + "<foreach collection='examDetailIds' item='examDetailId' open='(' separator=',' close=')'>"
            + "#{examDetailId}"
            + "</foreach>"
            + "</script>")
    List<ExamDetail> selectListByExamDetailIds(List<Long> examDetailIds);

    @Select("<script>"
            + "SELECT * FROM t_exam_detail WHERE subject_onlyid =#{subjectId} AND "
            + "id IN "
            + "<foreach collection='examDetailIds' item='examDetailId' open='(' separator=',' close=')'>"
            + "#{examDetailId}"
            + "</foreach>"
            + "</script>")
    List<ExamDetail> selectListByExamDetailIdsAndSubject(@Param("examDetailIds") List<Long> examDetailIds, @Param("subjectId") Long subjectId);

}